﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using Microsoft.Office.Interop.Excel;

namespace OA.Bll.Reimbursement
{
    public class ExcelExportHelper
    {
        public static void CreateCell(Application xapp, Worksheet xsheet, object value, int row, int col)
        {
            xsheet.Cells[row, col] = value;
            Range range3 = xsheet.get_Range(xapp.Cells[row, col], xapp.Cells[row, col]);

            range3.Font.Size = 8;
            SetBorderColor(xapp, xsheet, range3, range3);
        }
        public static void CreateCellNoBorder(Application xapp, Worksheet xsheet, object value, int row, int col)
        {
            xsheet.Cells[row, col] = value;
            Range range = xsheet.get_Range(xapp.Cells[row, col], xapp.Cells[row, col]);
            range.EntireColumn.AutoFit();
            range.Font.Size = 8;
            // SetBorderColor(xapp, xsheet, range3, range3);
        }
        public static void CreateCellNoBorder(Application xapp, Worksheet xsheet, string value, int row, int col, int row2, int col2, bool center)
        {
            xsheet.Cells[row, col] = value;
            Range range = xsheet.get_Range(xapp.Cells[row, col], xapp.Cells[row2, col2]);
            range.Font.Size = 8;
            range.EntireColumn.AutoFit();
            if (center)
                range.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            range.MergeCells = true;
            // SetBorderColor(xapp, xsheet, range3, range3);
        }

        public static void CreateCellTitle(Application xapp, Worksheet wsheet, string name, object value, int columnCounts)
        {
            wsheet.Name = name + xapp.Worksheets.Count;
            wsheet.Cells[1, 1] = value;
            Range rangeTitle = (wsheet.get_Range(xapp.Cells[1, 1], xapp.Cells[1, 1]));
            rangeTitle.Font.Bold = true;
            rangeTitle.RowHeight = 20;
            rangeTitle.Font.Size = 11;
            rangeTitle.Font.Name = "宋体";
            rangeTitle.Font.ColorIndex = 0;
            rangeTitle.Select();
            rangeTitle.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            Range range = wsheet.get_Range(xapp.Cells[1, 1], xapp.Cells[1, columnCounts]);
            range.MergeCells = true;
        }

        public static void SetBorderColor(Microsoft.Office.Interop.Excel.Application excel, _Worksheet xSt, Range sRange, Range tRange)
        {
            xSt.get_Range(sRange, tRange).Select();
            xSt.get_Range(sRange, tRange).EntireColumn.AutoFit();//自动调整列宽\
            //xSt.get_Range(sRange, tRange).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            tRange.HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
            xSt.get_Range(sRange, tRange).Borders.LineStyle = 0.5; //设置边框的宽度
            xSt.get_Range(sRange, tRange).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThin;
            xSt.get_Range(sRange, tRange).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThin;
            xSt.get_Range(sRange, tRange).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThin;
            xSt.get_Range(sRange, tRange).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThin;
        }
        /// <summary>
        /// 用于将excel表格中列索引转成列号字母，从A对应1开始
        /// </summary>
        /// <param name="index">列索引</param>
        /// <returns>列号</returns>
        public static string IndexToColumn(int index)
        {
            if (index <= 0)
            {
                throw new Exception("Invalid parameter");
            }
            index--;
            string column = string.Empty;
            do
            {
                if (column.Length > 0)
                {
                    index--;
                }
                column = ((char)(index % 26 + (int)'A')).ToString() + column;
                index = (int)((index - index % 26) / 26);
            } while (index > 0);
            return column;
        }

        /// <summary>
        /// 用于excel表格中列号字母转成列索引，从1对应A开始
        /// </summary>
        /// <param name="column">列号</param>
        /// <returns>列索引</returns>
        public static int ColumnToIndex(string column)
        {
            if (!Regex.IsMatch(column.ToUpper(), @"[A-Z]+"))
            {
                throw new Exception("Invalid parameter");
            }
            int index = 0;
            char[] chars = column.ToUpper().ToCharArray();
            for (int i = 0; i < chars.Length; i++)
            {
                index += ((int)chars[i] - (int)'A' + 1) * (int)Math.Pow(26, chars.Length - i - 1);
            }
            return index;
        }
    }
}
